/********************************************************************************************************

Script:			Exceptions
Author:			Richard Slim
Date:			12/12/2012
Description:	This script will process all exceptions stored in the exceptions table

Usage:			This is called from within the main SSIS task

2013-12-02		RSlim		Added the CleanupTime exception
				
********************************************************************************************************/

DECLARE @ExceptionType varchar(20)
DECLARE @ExceptionValue varchar(300)
DECLARE @ExceptionValue2 varchar(300)
DECLARE @Command VARCHAR(MAX)

SET @ExceptionType = ?
SET @ExceptionValue = ?
SET @ExceptionValue2 = ?

BEGIN

	IF @ExceptionType = 'DatabaseAddRemove'
	BEGIN

		-- Declare the variables
		DECLARE @Start int
		DECLARE @End int
		DECLARE @CurrentBackupString VARCHAR(300)

		--Grab the job step command
		SELECT @Command = sjs.command FROM msdb.dbo.sysjobsteps sjs
		JOIN msdb.dbo.sysjobs sj
		ON sjs.job_id = sj.job_id
		WHERE sjs.step_id = 1 AND sj.name = @ExceptionValue2

		--Check the Database portion against the exception, if it is different change it
		SELECT @Start = CHARINDEX('''',@Command,0) + 1

		SELECT @End = CHARINDEX('''',@Command,@Start) - @Start

		SELECT @CurrentBackupString = SUBSTRING(@Command,@Start,@End)

		IF @CurrentBackupString <> @ExceptionValue
		SELECT @Command = REPLACE(@Command, @CurrentBackupString, @ExceptionValue)

		--Modify the job with the new command
		EXEC msdb..sp_update_jobstep @job_name = @ExceptionValue2, @step_id = '1', @command = @Command

	END
  
	IF @ExceptionType = 'CleanupTime'
	BEGIN
		
		--Declare variable and set the cleanup time portion of the string
		DECLARE @String VARCHAR(100)
		SET @String = '@CleanupTime = ' + @ExceptionValue

		--grab the full command from the jobs system table and replace the cleanup time portion
		SELECT @Command = REPLACE(sjs.command,'@CleanupTime = 24',@String) FROM msdb.dbo.sysjobsteps sjs
		JOIN msdb.dbo.sysjobs sj
		ON sjs.job_id = sj.job_id
		WHERE sjs.step_id = 1 AND sj.name = @ExceptionValue2

		--Update the job
		EXEC msdb..sp_update_jobstep @job_name = @ExceptionValue2, @step_id = '1', @command = @Command

	END  

END